﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;

namespace DataAccessLayer
{
    public class DBBattery
    {
        private static SqlCommand dbCmd = null;

        public static void CreateBattery(int bmId)
        {

            string sql = @"insert into battery values (  100 , getdate() , '" + bmId + "'  , NULL )";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void AddBatteryToStation(int stationId)
        {
            string sql = @"insert into battery (percentCharged, activeFrom, bmId, stationId, timesCharged) values (  100 , getdate() , 1 , '" + stationId + "'  , 1)";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }
    
        
        public static void UpdateDateInBattery(int bId, DateTime acFrom)
        {
            string sql = @"Update battery Set activeFrom = '" + acFrom + "' where  batteryid = '" + bId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();

        }

        public static Battery GetBattery(int bId)
        {
            string sql = @"Select * from battery where batteryid = '" + bId + "'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Battery b;

            if (dbReader.Read())
                b = new Battery((int)dbReader["batteryId"], (int)dbReader["bmId"], (int)dbReader["percentCharged"],(int)dbReader["timesCharged"]);
            else
               b = null;   
           
            AccessDBSQLClient.Close();

            return b;

        }

        public static void LockBattery(int bId, bool locked, DateTime lockedUntil)
        {
            var lockedUntil2 = lockedUntil.ToString("yyyy-MM-dd HH:mm");

            string sql = @"Update battery Set isLocked = '" + locked + "', lockedUntil = '" + lockedUntil2 +  "' where  batteryid = '" + bId + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();

        }
      
        public static CarModel GetCarModel(int cmId)
        {

            string sql = @"select * from carmodel where cmId = '"+cmId+"'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            CarModel cm;

            if (dbReader.Read())
            cm = new CarModel((int)dbReader["cmId"], dbReader["cmDescription"].ToString(), (int)dbReader["kmOnFullCharge"]);
            else
                cm = null;
            AccessDBSQLClient.Close();

            return cm;

        }

        public static List<Battery> GetBatteriesInStation(int StationId)
        {
            List<Battery> returnList = new List<Battery>();

            string sql = @"select * from Battery where  stationId =  '" + StationId + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Battery b;

            while (dbReader.Read())
            {
                b = new Battery((int)dbReader["batteryId"], (int)dbReader["bmId"], (int)dbReader["percentCharged"],(int)dbReader["timesCharged"]);

               returnList.Add(b);

            }
            AccessDBSQLClient.Close();

                return returnList; 
        }

        public static List<Battery> GetAllBatteries()
        {
            List<Battery> returnList = new List<Battery>();

            string sql = @"select * from Battery ";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Battery b;

            while (dbReader.Read())
            {
                b = new Battery((int)dbReader["batteryId"], (int)dbReader["bmId"], (int)dbReader["percentCharged"], (int)dbReader["timesCharged"]);

                returnList.Add(b);

            }
            AccessDBSQLClient.Close();

            return returnList;
        }
      // 




        public static Battery GetAvailableBattery(int stationId, DateTime date)
        {

            var d = date.ToString("yyyy-MM-dd HH:mm");
           
            Battery b;
            
            string sql = @"select top 1 * from Battery where stationId = '" + stationId + "' and  activeFrom <= '" + d + "' and isLocked = 'false' ";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            if(dbReader.Read())
            {
                b = new Battery((int)(dbReader["batteryId"]));
                AccessDBSQLClient.Close();
            }
            else
                  {
                b = null;
            }

            dbReader.Close();
            AccessDBSQLClient.Close();
           
            return b; 
        }
 
    

         public static BatteryModel GetBatteryModel()
        {

         BatteryModel b;

            string sql = @"select top 1 * from BatteryModel";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            if (dbReader.Read())

                b = new BatteryModel((int)(dbReader["bmId"]));

            else

                b = null;

            AccessDBSQLClient.Close();

            return b;
         }

         public static void BatteryUnlockSweeper(DateTime unlockDate)
         {
             var d = unlockDate.ToString("yyyy-MM-dd HH:mm");

             string sql = @"update battery set isLocked = 'false' where lockeduntil < '" + d + "'";
  

             SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
             cmd.ExecuteNonQuery();
             AccessDBSQLClient.Close();
         }



    }
}
